﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Data.Odbc;
using MySql.Data.MySqlClient;

namespace S7.DBUtility
{
    public struct DBHelperParames
    {
        private string name;

        public string Name
        {
            get { return name; }
            set { name = value; }
        }
        private object value;

        public object Value
        {
            get { return this.value; }
            set { this.value = value; }
        }
    }

    /// <summary>
    /// DBHelper类用于封装高性能，可扩展的最佳实践.
    /// </summary>
    public abstract class DBHelper
    {
        public static readonly string connectionStringName = ConfigurationManager.AppSettings["ConnectionStringName"];
        public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        public static readonly string providerName = ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName;

        public static DbProviderFactory _factory = null;
        private static DbCommand cmd = null;
        private static DbConnection conn = null;

        static DBHelper()
        {
            try
            {
                CreateFactory();
            }
            catch (Exception ex)
            {

            }
        }

        public static void Close()
        {
            if (cmd != null) cmd.Dispose();
            if (conn.State != ConnectionState.Closed) conn.Close();
        }

        /// <summary>
        /// 创建数据库驱动实例
        /// </summary>
        /// <param name="providerName">数据库驱动名称</param>
        private static void CreateFactory()
        {
            switch (providerName)
            {
                case "System.Data.SqlClient":
                    _factory = SqlClientFactory.Instance;
                    break;
                case "System.Data.OleDb":
                    _factory = OleDbFactory.Instance;
                    break;
                case "System.Data.Odbc":
                    _factory = OdbcFactory.Instance;
                    break;
                case "System.Data.OracleClient":
                    _factory = OracleClientFactory.Instance;
                    break;
                case "MySql.Data.MySqlClient":
                    _factory = MySqlClientFactory.Instance;
                    break;
                default:
                    _factory = OdbcFactory.Instance;
                    break;
            }
        }

        /// <summary>
        /// 哈希表来存储缓存的参数
        /// </summary>
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 使用提供的参数在连接字符串中指定的数据库上执行一个DbCommand（不返回结果集）。
        /// </summary>
        /// <remarks>
        /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">一个有效的连接字符串。例如：SqlConnection</param>
        /// <param name="commandType">CommandType（存储过程，文本等）</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="commandParameters">用于执行命令的数组。例如：SqlParamters</param>
        /// <returns>返回该命令所影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            using (cmd = _factory.CreateCommand())
            {
                using (conn = _factory.CreateConnection())
                {
                    try
                    {
                        conn.ConnectionString = connectionString;
                        PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                        return ExecuteNonQuery(conn, cmdType, cmdText, commandParameters);
                    }
                    catch { Close(); return 0; }
                }
            }
        }

        /// <summary>
        /// 对现有的数据库连接，使用提供的参数执行一个 Command（不返回结果集）。
        /// </summary>
        /// <remarks>
        /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">现有的数据库连接。</param>
        /// <param name="commandType">CommandType（存储过程，文本等）</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="commandParameters">用于执行命令的数组。例如：SqlParamters</param>
        /// <returns>返回该命令所影响的行数</returns>
        public static int ExecuteNonQuery(IDbConnection connection, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            using (cmd = _factory.CreateCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
                catch { Close(); return 0; }
            }
        }

        /// <summary>
        /// 执行一个 Command（不返回结果集）使用现有的SQL事务使用提供的参数。
        /// </summary>
        /// <remarks>
        /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">现有的SQL事务</param>
        /// <param name="commandType">CommandType（存储过程，文本等）</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="commandParameters">用于执行命令的数组。例如：SqlParamters</param>
        /// <returns>返回该命令所影响的行数</returns>
        public static int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            using (cmd = _factory.CreateCommand())
            {
                try
                {
                    PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
                catch { Close(); return 0; }
            }
        }

        /// <summary>
        /// 执行一个 Command 反对使用提供的参数在连接字符串中指定的数据库返回一个结果集。
        /// </summary>
        /// <remarks>
        /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">一个有效的连接字符串。例如：SqlConnection 链接字符串</param>
        /// <param name="commandType">CommandType（存储过程，文本等）</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="commandParameters">用于执行命令的数组。例如：SqlParamters</param>
        /// <returns>IDataReader结果集。例如：SqlDataReader</returns>
        public static IDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            //如果该方法抛出异常，关闭连接，因为没有DataReader存在，因此commandBehaviour.CloseConnection将无法正常工作
            using (cmd = _factory.CreateCommand())
            {
                try
                {
                    conn = _factory.CreateConnection();

                    conn.ConnectionString = connectionString;

                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                    IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    cmd.Parameters.Clear();

                    return rdr;
                }
                catch { Close(); return null; }
            }
        }

        /// <summary>
        /// 执行一个 Command 反对使用提供的参数在连接字符串中指定的数据库返回一个结果集。
        /// </summary>
        /// <remarks>
        /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">一个有效的连接字符串。例如：SqlConnection 链接字符串</param>
        /// <param name="commandType">CommandType（存储过程，文本等）</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="commandParameters">用于执行命令的数组。例如：SqlParamters</param>
        /// <returns>IDataReader结果集。例如：SqlDataReader</returns>
        public static IDataReader ExecuteReader(DbConnection conn, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            //如果该方法抛出异常，关闭连接，因为没有DataReader存在，因此commandBehaviour.CloseConnection将无法正常工作
            using (cmd = _factory.CreateCommand())
            {
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                    IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    cmd.Parameters.Clear();

                    return rdr;
                }
                catch { Close(); return null; }
            }
        }

        /// <summary>
        /// 执行一个 Command，返回的第一条记录的第一列。
        /// </summary>
        /// <remarks>
        /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">一个有效的连接字符串.例如:SqlConnection连接字符串</param>
        /// <param name="commandType">CommandType（存储过程，文本等）</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="commandParameters">用于执行命令的数组。例如：SqlParamters</param>
        /// <returns>应使用Convert.To{类型转换为预期的类型的对象}</returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            using (cmd = _factory.CreateCommand())
            {
                using (conn = _factory.CreateConnection())
                {
                    try
                    {
                        conn.ConnectionString = connectionString;
                        PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                        object val = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        return val;
                    }
                    catch { Close(); return null; }
                }
            }
        }

        /// <summary>
        /// 执行一个 Command 对现有的数据库连接，使用提供的参数，返回的第一列的第一个记录。
        /// </summary>
        /// <remarks>
        /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">现有的数据库连接</param>
        /// <param name="commandType">CommandType（存储过程，文本等）</param>
        /// <param name="commandText">存储过程名称或T-SQL命令</param>
        /// <param name="commandParameters">用于执行命令的数组。例如：SqlParamters</param>
        /// <returns>应使用Convert.To{类型转换为预期的类型的对象}</returns>
        public static object ExecuteScalar(DbConnection connection, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            using (cmd = _factory.CreateCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
                catch { Close(); return null; }
            }
        }

        /// <summary>
        /// 添加参数数组的缓存
        /// </summary>
        /// <param name="cacheKey">缓存键名</param>
        /// <param name="cmdParms">要被缓存的数组。例如：SqlParamters</param>
        public static void CacheParameters(string cacheKey, params DbParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// 检索缓存的参数
        /// </summary>
        /// <param name="cacheKey">键</param>
        /// <returns>缓存参数数组</returns>
        public static DbParameter[] GetCachedParameters(string cacheKey)
        {
            DbParameter[] cachedParms = (DbParameter[])parmCache[cacheKey];

            if (cachedParms == null)
            {
                return null;
            }

            DbParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
            {
                clonedParms[i] = (DbParameter)((ICloneable)cachedParms[i]).Clone();
            }

            return clonedParms;
        }

        /// <summary>
        /// 准备执行的命令
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <param name="conn">Connection对象</param>
        /// <param name="trans">Transaction对象</param>
        /// <param name="cmdType">Command的类型。如存储过程或文本</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        private static void PrepareCommand(IDbCommand cmd, IDbConnection conn, IDbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                cmd.Parameters.Clear();

                foreach (DbParameter parm in cmdParms)
                {
                    DbParameter dparm = new SqlParameter(parm.ParameterName, parm.Value);
                    cmd.Parameters.Add(dparm);
                }
            }
        }

        public static T[] CreateParames<T, M>(M model)
        {
            return new T[] { };
        }
    }
}